import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.FileInputStream;
import java.io.FileOutputStream;

/**
 * Created by 123 on 2016/10/10.
 */
public class ExcelProcess {
    public static void ProcessExcel(String filePath){
        HSSFWorkbook workBook;
        try{
            FileInputStream fileIn=new FileInputStream(filePath);
            workBook=new HSSFWorkbook(fileIn);
            HSSFSheet sheet1=workBook.getSheetAt(0);
            String origin="";
            for(Row row:sheet1){
                for(Cell cell:row){
                    origin+=cell.getStringCellValue();
                }
            }
            System.out.println(origin);
            sheet1.addMergedRegion(new CellRangeAddress(0,1,0,1));
            for(int i=2;i<10;i++){
                HSSFRow row=sheet1.createRow(i);
                for(int j=2;j<10;j++){
                    HSSFCell cell=row.createCell(j);
                    cell.setCellValue(i*j);
                    if((j-1)%2==0&&(i-1)%2==0){
                        HSSFCellStyle style1=workBook.createCellStyle();
                        style1.setDataFormat(workBook.createDataFormat().getFormat("￥#,##0"));
                        cell.setCellStyle(style1);
                    }
                    else{
                        HSSFCellStyle style1=workBook.createCellStyle();
                        style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                        cell.setCellStyle(style1);
                    }
                    if(i==8&&j==9){
                        cell.setCellFormula("sum(I1:I8)");
                    }
                    else if(i==9&&j==8){
                        cell.setCellFormula("sum(A9:H9)");
                    }
                    else if(i==9&&j==9){
                        cell.setCellValue(origin);
                    }
                }
            }
            FileOutputStream fileOut = new FileOutputStream("Mod-"+filePath);
            workBook.write(fileOut);
            fileOut.close();
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}
